import time as tm
import datetime
import pymysql

# mysql数据库连接信息
#db = pymysql.connect(host='172.16.10.220', user='root', password='Pr0dkcd_')
db = pymysql.connect(host='localhost',port=23306, user='root', password='Pr0dkcd_')
cursor = db.cursor()

if __name__ == '__main__':

    error_shfit = "SELECT `name`,`code`,time,shift FROM kcdoc_temp.user_shift"
    cursor.execute(error_shfit)
    all_error = cursor.fetchall()
    for error in all_error:
        # 获取班次
        hi_shfit = "SELECT id,`day`FROM ( SELECT * FROM kcdoc.kc_shift_user_day UNION ALL SELECT * FROM kcdoc.hi_kc_shift_user_day ) aa WHERE aa.`day` >= '2022-07-01'  AND aa.user_no ={0}  AND aa.`day` = '{1}'".format(
            error[1], error[2])
        cursor.execute(hi_shfit)
        hiCount = cursor.fetchall()

        # 获取shift
        get_shfit_id = "SELECT id FROM kcdoc.kc_shift_definition WHERE name ='{0}'".format(error[3])

        cursor.execute(get_shfit_id)
        shfitId = cursor.fetchall()

        # 获取 detail 数据
        get_detail_id = "SELECT id,befor_time,end_time,type FROM kcdoc.kc_shift_detail_day WHERE shift_id = {0} and start_day ='{1}' and type = 0 ".format(shfitId[0][0], error[2])

        cursor.execute(get_detail_id)
        detail = cursor.fetchall()
        # 获取人员名字
        get_user_id ="SELECT user_id FROM kcdoc.sys_user WHERE user_no = {0} and user_realname = '{1}'".format(error[1],error[0])
        cursor.execute(get_user_id)
        user_id = cursor.fetchall()

        # 如果有排班
        if len(hiCount) >= 0:
            for index, value in enumerate(hiCount):
                cheakId = value[0]
                if len(detail) > index:
                    data = detail[index]
                    update_user = "UPDATE check_kc_shift_user_day set detail_day_id ={0},shift_id ={1},type={2} WHERE id = {3};".format(data[0],shfitId[0][0],data[3],cheakId)
                    print(update_user)
                else:
                    delet_user_sql = "DELETE from check_kc_shift_user_day where id = {0};".format(cheakId)
                    print(delet_user_sql)

            if len(detail) > len(hiCount):
                for data in detail[len(hiCount):]:
                    inser_user = "INSERT INTO check_kc_shift_user_day_insert (day,userId,detail_day_id,shift_id,user_no,data_type) values ('{0}','{1}',{2},{3},'{4}',{5});".\
                        format(error[2], user_id[0][0],data[0],shfitId[0][0],error[1],'1')
                    print(inser_user)

        else:
            for data in detail:
                inser_user = "INSERT INTO check_kc_shift_user_day_insert (day,userId,detail_day_id,shift_id,user_no,data_type) values ('{0}','{1}',{2},{3},'{4}',{5});".format(
                    error[2], user_id[0][0], data[0], shfitId[0][0], error[1], '1')
                print(inser_user)

